﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Xml.Linq;
using Wintellect.PowerCollections;

namespace Dating
{
    public static class DatabaseUtils
    {
        public static void CreateDatabase(string name)
        {
            SqlConnection dbConnection = new SqlConnection("Server=.\\SQLEXPRESS; " +
                "Database=master; Integrated Security=true");

            SqlCommand dropDatabaseCommand = new SqlCommand();
            dropDatabaseCommand.Connection = dbConnection;
            dropDatabaseCommand.CommandText =
                string.Format(
                    "IF EXISTS(SELECT * FROM sys.sysdatabases where name='{0}') \n" +
                    "BEGIN \n" +
                        "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE \n" +
                        "DROP DATABASE {0} \n" +
                    "END", name);

            SqlCommand CreateDatabaseCommand = new SqlCommand();
            CreateDatabaseCommand.Connection = dbConnection;
            CreateDatabaseCommand.CommandText =
                string.Format("CREATE DATABASE {0}", name);

            SqlCommand CreatePersonTable = new SqlCommand();
            CreatePersonTable.Connection = dbConnection;
            CreatePersonTable.CommandText =
                "USE " + name + "\n" +
                "CREATE TABLE Persons ( " +
                    "PersonID int IDENTITY PRIMARY KEY, " +
                    "Sex nvarchar(6), " +
                    "Name nvarchar(50) NOT NULL, " +
                    "Age int, " +
                    "HairColor nvarchar(20), " +
                    "EyeColor nvarchar(20), " +
                    "Height int)";

            SqlCommand CreatePreferencesTable = new SqlCommand();
            CreatePreferencesTable.Connection = dbConnection;
            CreatePreferencesTable.CommandText =
                "USE " + name + "\n" +
                "CREATE TABLE Preferences ( " +
                    "PreferencesID int IDENTITY PRIMARY KEY, " +
                    "PersonID int NOT NULL, " +
                    "Sex nvarchar(6), " +
                    "AgeMin int, " +
                    "AgeMax int, " +
                    "HairColor nvarchar(20), " +
                    "EyeColor nvarchar(20), " +
                    "HeightMin int, " +
                    "HeightMax int, " +
                    "CONSTRAINT FK_Preferences_Persons FOREIGN KEY(PersonID) REFERENCES Persons(PersonID))";

            dbConnection.Open();
            using (dbConnection)
            {
                dropDatabaseCommand.ExecuteNonQuery();
                CreateDatabaseCommand.ExecuteNonQuery();
                CreatePersonTable.ExecuteNonQuery();
                CreatePreferencesTable.ExecuteNonQuery();
            }
        }

        public static void FillDatabase(string personsPath, string databaseName)
        {
            SqlConnection dbConnection = new SqlConnection("Server=.\\SQLEXPRESS; " +
                "Database=" + databaseName + "; Integrated Security=true");
            dbConnection.Open();
            using (dbConnection)
            {
                XDocument personsDoc = XDocument.Load(personsPath);
                var guys = personsDoc.Descendants("guys");
                AddPersonsToDb(dbConnection, guys, "guy");
                var girls = personsDoc.Descendants("girls");
                AddPersonsToDb(dbConnection, girls, "girl");
            }
        }

        private static void AddPersonsToDb(SqlConnection dbConnection,
            IEnumerable<XElement> peopleToAdd, string sex)
        {
            var people = peopleToAdd.Descendants("person");
            foreach (var person in people)
            {
                string name = null;
                int? age = null;
                string hairColor = null;
                string eyeColor = null;
                int? height = null;
                foreach (var attribute in person.Attributes())
                {
                    switch (attribute.Name.ToString())
                    {
                        case "name":
                            name = attribute.Value;
                            break;
                        case "age":
                            age = int.Parse(attribute.Value);
                            break;
                        case "haircolor":
                            hairColor = attribute.Value;
                            break;
                        case "eyecolor":
                            eyeColor = attribute.Value;
                            break;
                        case "height":
                            height = int.Parse(attribute.Value);
                            break;
                        default:
                            throw new ArgumentException(
                                "Invalid attribute for person: {0}",
                                attribute.Name.ToString());
                    }
                }
                AddPersonToDb(dbConnection, name, age, hairColor, eyeColor, height, sex);
                FindPreferences(dbConnection, person.Descendants("preferences"));
            }
        }

        private static void AddPersonToDb(SqlConnection dbConnection,
            string name, int? age, string hairColor, string eyeColor, int? height, string sex)
        {
            SqlCommand insertCommand = new SqlCommand();
            insertCommand.Connection = dbConnection;
            insertCommand.CommandText =
                "INSERT INTO Persons (Name, Sex, Age, HairColor, EyeColor, Height)\n" +
                "VALUES (@name, @sex, @age, @hairColor, @eyeColor, @height)";
            insertCommand.Parameters.Add("@name", name);
            insertCommand.Parameters.Add("@sex", sex);
            insertCommand.Parameters.Add("@age", age);
            insertCommand.Parameters.Add("@hairColor", hairColor);
            insertCommand.Parameters.Add("@eyeColor", eyeColor);
            insertCommand.Parameters.Add("@height", height);
            insertCommand.ExecuteNonQuery();
        }

        private static void FindPreferences(
            SqlConnection dbConnection, IEnumerable<XElement> preferences)
        {
            SqlCommand findPersonId = new SqlCommand();
            findPersonId.Connection = dbConnection;
            findPersonId.CommandText = "SELECT MAX(PersonID) FROM Persons";
            int personID = (int)findPersonId.ExecuteScalar();
            foreach (var preference in preferences)
            {
                var guys = preference.Descendants("guy");
                AddPersonsPreferencesToDb(dbConnection, guys, personID, "guy");
                var girls = preference.Descendants("girl");
                AddPersonsPreferencesToDb(dbConnection, girls, personID, "girl");
            }
        }

        private static void AddPersonsPreferencesToDb(SqlConnection dbConnection,
            IEnumerable<XElement> persons, int personId, string sex)
        {
            List<int?> ageMins = new List<int?>();
            List<int?> ageMaxs = new List<int?>();
            List<string> hairColors = new List<string>();
            List<string> eyeColors = new List<string>();
            List<int?> heightMins = new List<int?>();
            List<int?> heightMaxs = new List<int?>();
            foreach (var person in persons)
            {
                int? ageMin = null;
                int? ageMax = null;
                string hairColor = null;
                string eyeColor = null;
                int? heightMin = null;
                int? heightMax = null;
                foreach (var item in person.Descendants())
                {
                    switch (item.Name.ToString())
                    {
                        case "age":
                            string[] ageString = item.Value.Split('-');
                            ageMin = int.Parse(ageString[0]);
                            ageMax = int.Parse(ageString[1]);
                            break;
                        case "height":
                            string[] heightString = item.Value.Split('-');
                            heightMin = int.Parse(heightString[0]);
                            heightMax = int.Parse(heightString[1]);
                            break;
                        case "haircolor":
                            hairColor = item.Value;
                            break;
                        case "eyecolor":
                            eyeColor = item.Value;
                            break;
                        default:
                            throw new ArgumentException(
                                "Invalid descendant: {0}", item.Name.ToString());
                    }
                }
                ageMins.Add(ageMin);
                ageMaxs.Add(ageMax);
                hairColors.Add(hairColor);
                eyeColors.Add(eyeColor);
                heightMins.Add(heightMin);
                heightMaxs.Add(heightMax);
                //AddPreferenceToDb(dbConnection, personId, sex, ageMin, ageMax, hairColor, eyeColor, heightMin, heightMax);
            }
            if (ageMins.Count > 0)
            {
                AddPreferencesToDb(dbConnection, personId, sex, ageMins, ageMaxs,
                hairColors, eyeColors, heightMins, heightMaxs);
            }
        }

        private static void AddPreferencesToDb(SqlConnection dbConnection,
            int personID, string sex, 
            List<int?> ageMins, List<int?> ageMaxs,
            List<string> hairColors, List<string> eyeColors,
            List<int?> heightMins, List<int?> heightMaxs)
        {
            StringBuilder sb = new StringBuilder(
                "INSERT INTO Preferences (PersonID, Sex, AgeMin, AgeMax, " +
                "HairColor, EyeColor, HeightMin, HeightMax) \n" + 
                "VALUES ");
            for (int i = 0; i < ageMins.Count; i++)
            {
                sb.Append(string.Format("({0}, '{1}', {2}, {3}, '{4}', '{5}', {6}, {7}), ",
                    personID, sex, ageMins[i], ageMaxs[i], hairColors[i],
                    eyeColors[i], heightMins[i], heightMaxs[i]));
            }
            SqlCommand insertCommand = new SqlCommand(sb.ToString(0, sb.Length - 2), dbConnection);
            insertCommand.ExecuteNonQuery();
        }

        public static OrderedMultiDictionary<string, string> FindDates(string databaseName)
        {
            var dates = new OrderedMultiDictionary<string, string>(false);
            SqlConnection dbConnection = new SqlConnection("Server=.\\SQLEXPRESS; " +
                "Database=" + databaseName + "; Integrated Security=true");
            dbConnection.Open();
            using (dbConnection)
            {
                SqlCommand findDatesCommand = new SqlCommand();
                findDatesCommand.Connection = dbConnection;
                findDatesCommand.CommandText =
                    "SELECT p.Name AS [PersonName], t.Name AS [TargetName] " + 
                    "FROM Persons AS p " +
                    "JOIN Preferences AS pr " +
                        "ON p.PersonID = pr.PersonID " +
                    "JOIN Persons AS t " +
                        "ON t.Sex = pr.Sex AND " +
                            "t.Age BETWEEN pr.AgeMin AND pr.AgeMax AND " +
                            "t.Height BETWEEN pr.HeightMin AND pr.HeightMax AND " +
                            "pr.EyeColor LIKE '%' + t.EyeColor + '%' AND " +
                            "pr.HairColor LIKE '%' + t.HairColor + '%' AND " +
                            "t.PersonID != p.PersonID";

                var commandResult = findDatesCommand.ExecuteReader();
                using (commandResult)
                {
                    while (commandResult.Read())
                    {
                        string firstPerson = commandResult["PersonName"].ToString();
                        string secondPerson = commandResult["TargetName"].ToString();
                        if (firstPerson.CompareTo(secondPerson) <= 0)
                        {
                            dates.Add(firstPerson, secondPerson);
                        }
                        else
                        {
                            dates.Add(secondPerson, firstPerson);
                        }
                    }
                }
            }
            return dates;
        }

        private static object GetDbValue (object value)
        {
            if (value != null)
            {
                return value;
            }
            else
            {
                return DBNull.Value;
            }
        }
    }
}
